package cn.kgc.javaweb.dao.impl;

import cn.kgc.javaweb.dao.UserDao;
import cn.kgc.javaweb.entity.SysUser;
import cn.kgc.javaweb.utils.DruidUtil;
import cn.kgc.javaweb.vo.UserVO;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: mosin
 * @version: v1.0  2022/6/7
 */
public class UserDaoImpl implements UserDao {

    @Override
    public SysUser selectByUsernameAndPassword(String username, String password) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "select id, username ,password,nick_name  AS nickName, role_id  roleId   from  sys_user where username = ? and password =?";
        try {
            SysUser user = queryRunner.query(sql, new BeanHandler<SysUser>(SysUser.class),username,password);
            return  user;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int addUser(String username, String securePassword) { // 注册

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "insert into sys_user (username,password) value(?,?)";

        try {
            int update = queryRunner.update(sql, username, securePassword);
            return update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int resetPasswordById(long id, String md5) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "update sys_user set password = ? where id = ?";
        try {
            int line = queryRunner.update(sql, md5, id);
            return line;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


        return 0;
    }

    @Override
    public Long selectTotal()  {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "select count(*)  from  sys_user";
        try {
            Long total = queryRunner.query(sql, new ScalarHandler<Long>());
            return total;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<UserVO> queryAll(int start, int size) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "SELECT  usr.id, username,password,phone,role_id roleId ,sex ,create_time createTime," +
                "state ,nick_name nickName ,role_name roleName  FROM sys_user usr left join sys_role" +
                " on usr.role_id = sys_role.id  LIMIT ? ,?";

        try {
            List<UserVO> userVOS = queryRunner.query(sql, new BeanListHandler<UserVO>(UserVO.class), start, size);
            return  userVOS;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int deleteById(Integer id) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql ="delete from sys_user where id = ?";
        try {
            int line = queryRunner.update(sql, id);
            return  line;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int updatePwd(Integer id, String s) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "update sys_user set password = ? where id = ?";
        try {
            int line = queryRunner.update(sql, s, id);
            return line;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public SysUser selectByUserName(String username) {
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "select  *  from sys_user  where username = ? ";
        try {
            SysUser user = queryRunner.query(sql, new BeanHandler<SysUser>(SysUser.class), username);
            return user;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int addUser(SysUser user) {
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = " insert into sys_user (username,password,nick_name,phone,sex,role_id)values(?,?,?,?,?,?)";
        try {
            int update = queryRunner.update(sql, user.getUsername(), user.getPassword(), user.getNickName(), user.getPhone(), user.getSex(), user.getRoleId());
            return  update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int updateUser(SysUser user) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "update sys_user set nick_name=? ,phone=? ,sex=?, role_id= ? where username = ?";
        try {
            int update = queryRunner.update(sql, user.getNickName(), user.getPhone(), user.getSex(), user.getRoleId(), user.getUsername());
            return  update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<SysUser> search(int start, Integer limit, String username) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "SELECT  usr.id, username,password,phone,role_id roleId ,sex ,create_time createTime," +
                "state ,nick_name nickName ,role_name roleName  FROM sys_user usr left join sys_role" +
                " on usr.role_id = sys_role.id where  1=1 ";

        ArrayList<Object> param = new ArrayList<>();

        if(!username.equals("")){

            sql+=" and username like ? ";
            param.add("%"+username+"%");
        }
        sql+=" limit ?,?";
        param.add(start);
        param.add(limit);

        System.out.println("sql = " + sql);
        System.out.println(param);
        try {
            List<SysUser> users = queryRunner.query(sql, new BeanListHandler<SysUser>(SysUser.class), param.toArray());
            return users;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public long selectTotalByUserName(String username) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "select count(*) from sys_user where 1=1 ";
        ArrayList<Object> param = new ArrayList<>();
        if(!username.equals("")){
            sql+=" and username like ? ";
            param.add("%"+username+"%");
        }
        System.out.println("sql = " + sql);
        try {
            Long query = queryRunner.query(sql, new ScalarHandler<Long>(), param.toArray());
            return  query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public Integer updateState(String id, Integer state) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "update sys_user set state = ? where id = ?";
        try {
            int update = queryRunner.update(sql, state, id);
            return  update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}
